
* The following Stata packages need to be installed in order to run this program: ftools, reghdfe, egenmore, mdesc

****************************************************
* PREAMBLE
****************************************************	
		
cap log close
cls
clear
set more off

global path "..." 
* This is the project folder (where we have this .do file)
* The data files need to be put in a subfolder DATA
* A folder OUTPUT TABLES and a folder OUTPUT FIGURES need to be created as subfolders of the $path folder 



*************************************************
*** TABLE 1: Frequency table
*************************************************

	use "$path/DATA/CKP_refin.dta", clear 

	* Only keep mortgages
	keep if c_type_krediet==30

	* Drop deleted mortgages
	drop if x_loan_status=="deleted":label_loan_status
	
	* Generate variable for new loans
	gen New= 0
	replace New = 1 if x_loan_status=="new":label_loan_status | x_loan_status=="new_past":label_loan_status | x_loan_status=="ongoing current":label_loan_status 
	gen New_amount = .  
	replace New_amount = a_pskr if New==1
	gen New_maturity = . 
	replace New_maturity = q_pskr_term if New==1
	gen New_rate = . 
	replace New_rate = x_est_rate_ann if New==1

	* Sort on borrower id, loan id, year
	sort s_rrnr_id s_pskr_id year

	* Generate variables for all loans
	gen All_amount = a_pskr if s_pskr_id!=s_pskr_id[_n-1]
	gen All_maturity = q_pskr_term if s_pskr_id!=s_pskr_id[_n-1]
	gen All_rate = x_est_rate_ann if s_pskr_id!=s_pskr_id[_n-1]

	* Generate variables to count borrowers, loans, municipalities (INS codes)
	egen All_borrower_nvals=nvals(s_rrnr_id)
	egen New_borrower_nvals=nvals(s_rrnr_id) if New==1
	egen All_loan_nvals=nvals(s_pskr_id)
	egen New_loan_nvals=nvals(s_pskr_id) if New==1
	egen All_INS_nvals=nvals(INS)
	egen New_INS_nvals=nvals(INS) if New==1
	*
	bys year: egen All_borrower_year=nvals(s_rrnr_id)
	bys year: egen New_borrower_year=nvals(s_rrnr_id) if New==1

	* Table
	label var All_borrower_nvals "Borrowers" 
	label var New_borrower_nvals "Borrowers (new mortgages)"
	label var All_loan_nvals "Loans"
	label var New_loan_nvals "Loans (new mortgages)" 
	label var All_INS_nvals "Municipalities"
	*
	estimates clear
	estpost summarize All_borrower_nvals New_borrower_nvals All_loan_nvals New_loan_nvals All_INS_nvals, d
	esttab using "$path/OUTPUT TABLES/data_mortgages.tex", ///
	cells("max(label(Count) fmt(%9.0fc))") ///
	alignment(r) ///
	label /// make use of variable labels
	noobs nonumber /// do not display number of observations or row numbers
	replace


	
*************************************************
*** FIGURE 2: Mortgages and refinancing activity
*************************************************

*** Panel (a): Characteristics of new mortgage loans 

	collapse (count) N_Loans=c_type_krediet (sum) N_New=New (max) N_All_borrower_year=All_borrower_year N_New_borrower_year=New_borrower_year /// 
	(median) P50_New_amount=New_amount P50_New_maturity=New_maturity P50_New_rate=New_rate ///
	(mean) Mean_New_amount=New_amount Mean_New_maturity=New_maturity Mean_New_rate=New_rate, by(year)
	*
	replace P50_New_amount = P50_New_amount/1000
	replace Mean_New_amount = Mean_New_amount/1000
	replace P50_New_rate = P50_New_rate*100
	replace Mean_New_rate = Mean_New_rate*100
	format N_* %12.0fc
	*
	twoway (line P50_New_amount year, lcolor(navy)) ///
			(line Mean_New_amount year, lpattern(dash) lcolor(navy)) ///
			(line P50_New_maturity year, lcolor(orange)) ///
			(line Mean_New_maturity year, lpattern(dash) lcolor(orange)) ///
			(scatter P50_New_rate year, yaxis(2) msymbol(circle_hollow) mcolor(red)) ///
			(scatter Mean_New_rate year, yaxis(2) msymbol(triangle_hollow) mcolor(black)), ///
			graphregion(color(white)) bgcolor(white) ///
			ytitle("Amount and loan term") ///
			ytitle("Interest rate (%)", axis(2)) ///
			ylabel(0(60)300) ///
			ylabel(0(1)5, axis(2)) ///
			xtitle("") ///
			xlabel(2006(2)2022) ///
			legend(order(1 "Median amount (EUR th.)" ///
				2 "Mean amount (EUR th.)" ///
				3 "Median loan term (months)" ///
				4 "Mean loan term (months)" /// 
				5 "Median interest rate (%)" ///
				6 "Mean interest rate (%)") ///
				position(bottom) cols(2)) // move the legend to the bottom of the graph
	graph export "$path/OUTPUT FIGURES/data_mortgages_a.png", replace as(png)

*** Panel (b): Fraction of loans issued in a given year that are still in the pool of outstanding loans by years since issuance
 
     use "$path/DATA/CKP_refin.dta", clear
    
     * Drop deleted loans
     drop if x_loan_status=="deleted":label_loan_status
    
     * Generate variable measuring loan maturity in years
     gen IssuanceMatu=x_loan_end_year-x_loan_start_year
 
     * Generate variables measuring number of loans and total outstanding amount by year x loan start year x maturity at issuance
     collapse (count) s_pskr_id, by(year x_loan_start_year IssuanceMatu)
 
     * Generate ratios of number of loans to initial number of loans
     gen T = year-x_loan_start_year  
     drop if year<2006
     drop if x_loan_start_year<2006
     drop if T<0
     bysort IssuanceMatu x_loan_start_year(year): gen Total_N_Loans=s_pskr_id[1]
     bysort IssuanceMatu: gen Ratio_N_Loans=s_pskr_id/Total_N_Loans
 
     * Graph
	 format Ratio_N_Loans %9.1fc
     twoway (line Ratio_N_Loans T if x_loan_start_year==2006,lcolor(black) lpattern(solid)) ///
           (line Ratio_N_Loans T if x_loan_start_year==2010,lcolor(red) lpattern(dash)) ///
           (line Ratio_N_Loans T if x_loan_start_year==2014,lcolor(navy) lpattern(longdash)) ///
           (line Ratio_N_Loans T if x_loan_start_year==2018,lcolor(orange) lpattern(dash_dot)) ///
           if (IssuanceMatu==20 & T<20), ///
                     xlabel(#15) legend(order(1 "2006" 2 "2010" 3 " 2014" 4 "2018") pos(6) cols(4)) ///
                     ylabel(0(0.2)1) ytitle(Fraction still outstanding) xtitle(Years since issuance)
     graph export "$path/OUTPUT FIGURES/data_mortgages_b.png", replace



*************************************************
*** FIGURE 3: Refinancing activity and interest rate gains
*************************************************

*** Panel (a): Share of mortgages being refinanced in the next calendar year

	use  "$path/DATA/CKP_refin.dta", clear 
	
	* Only keep mortgages
	keep if c_type_krediet==30
	
	* Drop deleted mortgages
	drop if x_loan_status=="deleted":label_loan_status
	
	* Drop year 2022 (as figures based on future refinancing)
	drop if year==2022
	
	* Graph
	graph bar x_refinanced_internal_future x_refinanced_external_future, over(year, label(alternate)) stack ///
		graphregion(color(white)) bgcolor(white) ///
		bar(1, color(navy)) bar(2, color(cranberry)) ///
		ytitle(Share) ylabel(,format(%3.2f)) ///
		legend(label(1 "% refinanced int.") label(2 "% refinanced ext.") position(bottom))
	graph export "$path/OUTPUT FIGURES/data_refinancing_a.png", replace as(png)

*** Panel (b) : Interest differential on old vs new loans

	use  "$path/DATA/CKP_refin.dta", clear 
	*
	gen old_rate=x_est_rate_ann if x_refinanced==1
	gen new_rate=x_est_rate_ann if x_refinancing==1
	sort s_rrnr_id s_pskr_id year
	bysort s_rrnr_id year: egen ave_old_rate=mean(old_rate)
	bysort s_rrnr_id year: egen ave_new_rate=mean(new_rate)
	*
	gen RealizedGain=.
	replace RealizedGain=-(ave_new_rate-ave_old_rate) if x_refinanced==1
	*
	gen RealizedGain_internal=.
	replace RealizedGain_internal=RealizedGain if x_refinanced_internal==1
	gen RealizedGain_external=.
	replace RealizedGain_external=RealizedGain if x_refinanced_external==1
	*
	gen x_refinanced_ext_vs_int = .
	replace x_refinanced_ext_vs_int=1 if x_refinanced_external==1
	replace x_refinanced_ext_vs_int=0 if x_refinanced_internal==1
	*
	keep if ~mi(x_refinanced_ext_vs_int)
	collapse (median) RealizedGain_internal RealizedGain_external, by(year x_refinanced_ext_vs_int)
	replace RealizedGain_internal=RealizedGain_internal*100
	replace RealizedGain_external=RealizedGain_external*100

	* Graph
	format RealizedGain_internal %9.1fc
	format RealizedGain_external %9.1fc
	twoway (line RealizedGain_internal year if x_refinanced_ext_vs_int==0, lcolor(navy)) ///
			(line RealizedGain_external year if x_refinanced_ext_vs_int==1, lpattern(dash) lcolor(red)), ///
		xtitle("") ytitle(Interest rate differential (p.p.)) xlabel(2007(2)2022) ylabel(-0.5(0.5)2) ///
		legend(label(1 "Internal refinancing") label(2 "External refinancing") position(bottom) cols(2))
	graph export "$path/OUTPUT FIGURES/data_refinancing_b.png", replace as(png)

	

*************************************************
*** FIGURE 4: Refinancing propensities as function of loan characteristics
*************************************************

use  "$path/DATA/CKP_refin.dta", clear 

	* Only keep mortgages
	keep if c_type_krediet==30
	* Drop deleted mortgages
	drop if x_loan_status=="deleted":label_loan_status
	* Keep data for one year	
	keep if year==2014

*** Panel (a): Refinancing propensity as function of remaining maturity groups (in 2014)

	mdesc x_residual_term
	sum x_residual_term, d
	xtile d_remaining = x_residual_term, nq(10)
	tab d_remaining, m
	bysort d_remaining: egen d_remaining_median=median(x_residual_term)
	tab d_remaining_median, m
	*
	graph bar x_refinanced_internal_future x_refinanced_external_future, over(d_remaining) ///
		bar(1, color(navy)) bar(2, color(cranberry)) ///
		ytitle(Share) ylabel(,format(%3.2f)) b1title(Decile) ///
		legend(label(1 "% refinanced internally") label(2 "% refinanced externally") position(bottom) cols(2))
	graph export "$path/OUTPUT FIGURES/data_propensities_a.png", replace as(png)

*** Panel (b): Refinancing propensity as function of outstanding balance groups (in 2014)

	mdesc x_outstanding_amount
	sum x_outstanding_amount, d
	xtile d_outstanding = x_outstanding_amount, nq(10)
	tab d_outstanding, m
	bysort d_outstanding: egen d_outstanding_median=median(x_outstanding_amount)
	tab d_outstanding_median, m
	*
	graph bar x_refinanced_internal_future x_refinanced_external_future, over(d_outstanding) ///
		bar(1, color(navy)) bar(2, color(cranberry)) ///
		ytitle(Share) ylabel(,format(%3.2f)) b1title(Decile) ///
		legend(label(1 "% refinanced internally") label(2 "% refinanced externally") position(bottom) cols(2))
	graph export "$path/OUTPUT FIGURES/data_propensities_b.png", replace as(png)



*************************************************/
*** Construct loan-level data set to be used in analysis
*************************************************

	use "$path/DATA/CKP_refin.dta", clear 

*** Sample selection

	keep if c_type_krediet==30

*** Merge with relevant data

	* Merge with population data
	merge m:1 INS using "$path/DATA/Other_population.dta"
	tab INS if _merge==1 & year<2021, m
	drop if _merge==2
	drop _merge
	* Merge with local bank market competitiveness data
	merge m:1 INS year using "$path/DATA/Data_bank_competition_mt.dta"
	tab year if _merge==1, m
	tab INS if _merge==1, m
	drop if _merge==2
	drop _merge
	* Merge with data on lender's local competitive position 
	merge m:1 INS year bank_name using "$path/DATA/Data_bank_competition_bmt.dta"
	tab year if _merge==1, m
	tab INS if _merge==1, m
	tab bank if _merge==1, m
	drop if _merge==2
	drop _merge
	* Merge with consultations data 
	merge m:1 s_rrnr_id year using "$path/DATA/CKP_consultations.dta"
	drop _merge
	* Merge with data on best available rates
	merge m:1 INS year using "$path/DATA/Data_best_rates_mt.dta"
	drop _merge
	
*** Create variable for share of delinquent loans

	preserve
	duplicates drop s_rrnr_id year, force
	collapse (mean) share_delinq=x_delinq_borr, by(INS year)
	tempfile delinquencies
	save `delinquencies',replace
	restore
	merge m:1 INS year using `delinquencies'
	drop _merge
	
*** Create variable indicating whether borrower has other bank

	gen HasOtherBank=0
	replace HasOtherBank=1 if x_bank_relations>1 & x_bank_relations!=.

*** Create bank branch variable scaled by municipality surface

	gen Count_tot_km2 = Count / z_Superficieaukm

*** Choice of controls for potential gains

	bysort year: mdesc x_gross_gain_eur // note: missing if loan stems from before 2007 because no MIR data available
	sum x_gross_gain_eur, d 
	*
	gen gain_control = x_gross_gain_eur/1000
	gen gain_control_2 = gain_control^2
	gen gain_control_3 = gain_control^3
	gen gain_control_4 = gain_control^4
	* 
	label var gain_control "Gross gain (EUR th.)"
	label var gain_control_2 "Gross gain (EUR th.) ^2"
	label var gain_control_3 "Gross gain (EUR th.) ^3"
	label var gain_control_4 "Gross gain (EUR th.) ^4"
	
*** Create variable measuring gain from refinancing

	gen old_rate=x_est_rate_ann if x_refinanced==1 
	gen new_rate=x_est_rate_ann if x_refinancing==1
	sort s_rrnr_id s_pskr_id year
	bysort s_rrnr_id year: egen ave_old_rate=mean(old_rate)
	bysort s_rrnr_id year: egen ave_new_rate=mean(new_rate)
	*
	preserve
	keep s_rrnr_id year ave_new_rate ave_old_rate
	duplicates drop
	sort s_rrnr_id year
	xtset s_rrnr_id year
	bysort s_rrnr_id: gen Fut_new_rate=f1.ave_new_rate
	bysort s_rrnr_id: gen Fut_old_rate=f1.ave_old_rate
	drop ave_new_rate ave_old_rate
	tempfile realized
	save `realized',replace
	restore
	merge m:1 s_rrnr_id year using `realized'
	drop _merge
	
*** Create additional variables and controls

	replace Fut_new_rate = Fut_new_rate*100
	replace Fut_old_rate = Fut_old_rate*100
	replace Fut_old_rate=. if Fut_old_rate!=. & Fut_new_rate==.
	replace Fut_new_rate=. if Fut_new_rate!=. & Fut_old_rate==.
	replace x_start_average = x_start_average*100
	*
	gen premium = (Fut_old_rate - x_start_average)
	gen ext_start = x_refinanced_external_future * x_start_average
	gen ext_premium = x_refinanced_external_future * premium
	*
	gen x_outstanding_log = log(x_outstanding_amount)
	gen age_control = age_group-1
	replace age_control = 1 if age_control==0 // pool 18-24 year olds with 25-34 year olds
	label define age_labels 1 "Age <35" ///
		2 "Age 35-44"  ///
		3 "Age 45-54" ///
		4 "Age 55-64" ///
		5 "Age >=65" 
	label values age_control age_labels

*** Search-related outcome variables	

	replace N_otherbank=0 if N_otherbank==.
	gen N_otherbank_d = 1 if N_otherbank!=.
	replace N_otherbank_d = 0 if N_otherbank==0
	
*** Labels 

	label var x_refinanced_future "All refinancing"
	label var x_refinanced_internal_future "Internal refinancing"
	label var x_refinanced_external_future "External refinancing"
	*
	label var Fut_old_rate "Old rate (\%)"
	label var Fut_new_rate "New rate (\%)"
	*
	label var x_start_average	"Local average rate at time of origination"
	label var ext_start "External x local average rate"
	label var premium	"Diff. of old rate with local average"
	label var ext_premium "External x difference"
	*
	label var N_otherbank_d "Consult (dummy)"
	label var N_otherbank "Consult (count)"
	*
	label var Count_tot_km2 "Branches per km2"
	label var share_delinq "Share of delinquent borrowers"
	label var best_rate "Best available rate (\%)"
	label var x_delinq_borr "Borrower has been delinquent"
	label var HasOtherBank "Borrower has other bank"
	label var BankBranchIsPresentInINS "Lender has local branch"
	*
	label var x_est_rate_ann "Rate on current mortgage"
	label var x_outstanding_log "Outstanding balance (logged)"
	label var x_residual_term "Residual term"
	
	
*************************************************
*** TABLE 2: Descriptive statistics
*************************************************

preserve

keep if x_refinanced_future!=.

estimates clear
estpost summarize x_refinanced_future x_refinanced_internal_future x_refinanced_external_future Fut_old_rate Fut_new_rate  N_otherbank_d N_otherbank Count_tot_km2 share_delinq best_rate HasOtherBank x_delinq_borr BankBranchIsPresentInINS, d
esttab using "$path/OUTPUT TABLES/descriptives.tex", ///
cells("count(label(N) fmt(%9.0fc)) mean(label(Mean) fmt(%9.2fc)) sd(label(S.D.)  fmt(%9.2fc)) p10(label(P10)  fmt(%9.2fc)) p50(label(P50)  fmt(%9.2fc)) p90(label(P90)  fmt(%9.2fc))") ///
label /// make use of variable labels
align(rrrrrr) ///
noobs nonumber ///
replace

restore


*************************************************
*** TABLE 3: Sensitivity of refinancing rate to rate at origination
*************************************************

eststo reg_rates: reghdfe Fut_new_rate x_refinanced_external_future x_start_average ext_start premium ext_premium if x_refinanced_future==1, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local origxyear "Yes":reg_rates
	estadd local bankxzip   "Yes":reg_rates
	estadd local bankxyear  "Yes":reg_rates
	estadd local INSxyear   "Yes":reg_rates
	estadd local agegroup	"Yes":reg_rates

esttab reg_rates ///
	using "$path/OUTPUT TABLES/reg_rates.tex", ///
b(%8.5f) ///
se(%8.5f) /// display standard errors instead of t-stats
star(* 0.10 ** 0.05 *** 0.01) /// display significance stars
legend /// adding legend
scalars( "origxyear Origination year x year F.E.?" "bankxzip Bank x municipality F.E.?" "bankxyear Bank x year F.E.?" "INSxyear Municipality x year F.E.?" "agegroup Borrower age group F.E.?"   "N Observations" "r2 R squared") /// scalars to be added to footer
sfmt(%8.3f  %8.3f  %8.3f  %8.3f %8.3f  %15.0fc %8.3f ) /// format for scalars
noobs ///
compress /// reduce horizontal spacing
label /// make use of variable labels
mtitles ("New rate") /// model titles
keep(x_start_average ext_start premium ext_premium x_refinanced_external_future) ///
order(x_start_average ext_start premium ext_premium x_refinanced_external_future)  ///
replace // overwrite existing file



*************************************************
*** TABLE 4: Determinants of refinancing activity
*************************************************

* Control for mortgage origination year x current year
eststo reg_local4: reghdfe x_refinanced_future share_delinq Count_tot_km2 x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term, absorb(centuryyearpos#year bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local4
	estadd local gain 		"No":reg_local4
	estadd local origxyear  "Yes":reg_local4
	estadd local bankxzip   "Yes":reg_local4
	estadd local bankxyear  "Yes":reg_local4
	estadd local agegroup	"Yes":reg_local4
	estadd local borrower   "No":reg_local4
eststo reg_local5: reghdfe x_refinanced_internal_future share_delinq Count_tot_km2 x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term, absorb(centuryyearpos#year bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local5
	estadd local gain 		"No":reg_local5
	estadd local origxyear  "Yes":reg_local5
	estadd local bankxzip   "Yes":reg_local5
	estadd local bankxyear  "Yes":reg_local5
	estadd local agegroup	"Yes":reg_local5
	estadd local borrower   "No":reg_local5	
eststo reg_local6: reghdfe x_refinanced_external_future share_delinq Count_tot_km2 x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term, absorb(centuryyearpos#year bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local6
	estadd local gain 		"No":reg_local6
	estadd local origxyear  "Yes":reg_local6
	estadd local bankxzip   "Yes":reg_local6
	estadd local bankxyear  "Yes":reg_local6
	estadd local agegroup	"Yes":reg_local6
	estadd local borrower   "No":reg_local6
* Control for expected refinancing gains
eststo reg_local1: reghdfe x_refinanced_future share_delinq Count_tot_km2 best_rate x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank gain_control*, absorb(bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local1
	estadd local gain 		"Yes":reg_local1
	estadd local origxyear  "No":reg_local1
	estadd local bankxzip   "Yes":reg_local1
	estadd local bankxyear  "Yes":reg_local1
	estadd local agegroup	"Yes":reg_local1
	estadd local borrower   "No":reg_local1
eststo reg_local2: reghdfe x_refinanced_internal_future share_delinq Count_tot_km2 best_rate x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank gain_control*, absorb( bank#INS bank#year age_control ) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local2
	estadd local gain 		"Yes":reg_local2
	estadd local origxyear  "No":reg_local2
	estadd local bankxzip   "Yes":reg_local2
	estadd local bankxyear  "Yes":reg_local2
	estadd local agegroup	"Yes":reg_local2
	estadd local borrower   "No":reg_local2
eststo reg_local3: reghdfe x_refinanced_external_future share_delinq Count_tot_km2 best_rate x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank gain_control*, absorb( bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_local3
	estadd local gain 		"Yes":reg_local3
	estadd local origxyear  "No":reg_local3
	estadd local bankxzip   "Yes":reg_local3
	estadd local bankxyear  "Yes":reg_local3
	estadd local agegroup	"Yes":reg_local3
	estadd local borrower   "No":reg_local3 

esttab reg_local4 reg_local5 reg_local6 reg_local1 reg_local2 reg_local3  ///
	using "$path/OUTPUT TABLES/reg_local.tex", ///
b(%8.5f) ///
se(%8.5f) /// display standard errors instead of t-stats
star(* 0.10 ** 0.05 *** 0.01) /// display significance stars
legend /// adding legend
scalars("rate Rate on current mortgage?" "origxyear Additional loan-level controls?" "gain Refinancing gain polynomial?" "bankxzip Bank x municipality F.E.?" "bankxyear Bank x year F.E.?" "agegroup Borrower age group F.E.?" "N Observations" "r2 R squared") /// scalars to be added to footer
sfmt(%8.3f %8.3f  %8.3f  %8.3f  %8.3f %8.3f %15.0fc %8.3f ) /// format for scalars
compress /// reduce horizontal spacing
label /// make use of variable labels
noobs ///
mtitles ( All Internal External All Internal External ) /// model titles
keep (Count_tot_km2 share_delinq best_rate HasOtherBank x_delinq_borr BankBranchIsPresentInINS) /// keep individual coefficients 
order(Count_tot_km2 share_delinq best_rate HasOtherBank x_delinq_borr BankBranchIsPresentInINS) ///
replace // overwrite existing file



*************************************************
*** TABLE 5: Determinants of consultations by outside banks
*************************************************

* Control for mortgage origination year x current year
eststo reg_search_local3: reghdfe N_otherbank_d share_delinq Count_tot_km2 x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term, absorb(centuryyearpos#year bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_search_local3
	estadd local gain 		"No":reg_search_local3
	estadd local oldrate   "Yes":reg_search_local3
	estadd local origxyear  "Yes":reg_search_local3
	estadd local bankxzip   "Yes":reg_search_local3
	estadd local bankxyear  "Yes":reg_search_local3
	estadd local agegroup	"Yes":reg_search_local3
	estadd local borrower   "No":reg_search_local3
eststo reg_search_local4: reghdfe N_otherbank share_delinq Count_tot_km2 x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term, absorb(centuryyearpos#year bank#INS bank#year age_control) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_search_local4
	estadd local gain 		"No":reg_search_local4
	estadd local oldrate   "Yes":reg_search_local4
	estadd local origxyear  "Yes":reg_search_local4
	estadd local bankxzip   "Yes":reg_search_local4
	estadd local bankxyear  "Yes":reg_search_local4
	estadd local agegroup	"Yes":reg_search_local4
	estadd local borrower   "No":reg_search_local4
* Control for expected refinancing gains
eststo reg_search_local1: reghdfe N_otherbank_d share_delinq Count_tot_km2 best_rate gain_control* x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank, absorb(bank#INS bank#year age_control ) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_search_local1
	estadd local gain 		"Yes":reg_search_local1
	estadd local oldrate   "Yes":reg_search_local1
	estadd local origxyear  "No":reg_search_local1
	estadd local bankxzip   "Yes":reg_search_local1
	estadd local bankxyear  "Yes":reg_search_local1
	estadd local agegroup	"Yes":reg_search_local1
	estadd local borrower   "No":reg_search_local1
eststo reg_search_local2: reghdfe N_otherbank share_delinq Count_tot_km2 best_rate gain_control* x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank, absorb(bank#INS bank#year age_control ) vce(cluster s_rrnr_id INS)
	estadd local rate 		"Yes":reg_search_local2
	estadd local gain 		"Yes":reg_search_local2
	estadd local oldrate   "Yes":reg_search_local2
	estadd local origxyear  "No":reg_search_local2
	estadd local bankxzip   "Yes":reg_search_local2
	estadd local bankxyear  "Yes":reg_search_local2
	estadd local agegroup	"Yes":reg_search_local2
	estadd local borrower   "No":reg_search_local2

esttab reg_search_local3 reg_search_local4 reg_search_local1 reg_search_local2  ///
	using "$path/OUTPUT TABLES/reg_search_local.tex", ///
b(%8.5f) ///
se(%8.5f) /// display standard errors instead of t-stats
star(* 0.10 ** 0.05 *** 0.01) /// display significance stars
legend /// adding legend
scalars("rate Rate on current mortgage?" "origxyear Additional loan-level controls?" "gain Refinancing gain polynomial?" "bankxzip Bank x municipality F.E.?" "bankxyear Bank x year F.E.?" "agegroup Borrower age group F.E.?" "N Observations" "r2 R squared") /// scalars to be added to footer
sfmt(%8.3f %8.3f %8.3f %8.3f %8.3f  %8.3f %15.0fc %8.3f ) /// format for scalars
compress /// reduce horizontal spacing
label /// make use of variable labels
noobs ///
mtitles ("Consult (dummy)" "Consult (count)" "Consult (dummy)" "Consult (count)") /// model titles
keep (Count_tot_km2 share_delinq best_rate HasOtherBank x_delinq_borr BankBranchIsPresentInINS) /// keep individual coefficients 
order(Count_tot_km2 share_delinq best_rate HasOtherBank x_delinq_borr BankBranchIsPresentInINS) ///
replace // overwrite existing file



*************************************************
*** TABLE 6: Borrower characteristics and refinancing decisions
*************************************************

eststo reg_borrower1: reghdfe x_refinanced_internal_future x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term ib1.age_control##c.HasOtherBank, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local gain 		"No":reg_borrower1
	estadd local origxyear  "Yes":reg_borrower1
	estadd local bankxzip   "Yes":reg_borrower1
	estadd local bankxyear  "Yes":reg_borrower1
	estadd local INSxyear   "Yes":reg_borrower1
	estadd local agegroup	"Yes":reg_borrower1
	estadd local subsample  "No":reg_borrower1
eststo reg_borrower2: reghdfe x_refinanced_external_future x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term ib1.age_control##c.HasOtherBank, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local gain 		"No":reg_borrower2
	estadd local origxyear  "Yes":reg_borrower2
	estadd local bankxzip   "Yes":reg_borrower2
	estadd local bankxyear  "Yes":reg_borrower2
	estadd local INSxyear   "Yes":reg_borrower2
	estadd local agegroup	"Yes":reg_borrower2
	estadd local subsample  "No":reg_borrower2
eststo reg_borrower3: reghdfe x_refinanced_external_future x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term ib1.age_control##c.HasOtherBank if x_refinanced_future==1, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local gain 		"No":reg_borrower3
	estadd local origxyear  "Yes":reg_borrower3
	estadd local bankxzip   "Yes":reg_borrower3
	estadd local bankxyear  "Yes":reg_borrower3
	estadd local INSxyear   "Yes":reg_borrower3
	estadd local agegroup	"Yes":reg_borrower3
	estadd local subsample  "Yes":reg_borrower3
eststo reg_search_borrower1: reghdfe N_otherbank_d x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term ib1.age_control##c.HasOtherBank, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local gain 		"No":reg_search_borrower1
	estadd local oldrate   "Yes":reg_search_borrower1
	estadd local origxyear  "Yes":reg_search_borrower1
	estadd local bankxzip   "Yes":reg_search_borrower1
	estadd local bankxyear  "Yes":reg_search_borrower1
	estadd local INSxyear   "Yes":reg_search_borrower1
	estadd local agegroup	"Yes":reg_search_borrower1
	estadd local subsample  "No":reg_search_borrower1
eststo reg_search_borrower2: reghdfe N_otherbank x_est_rate_ann x_delinq_borr BankBranchIsPresentInINS HasOtherBank x_outstanding_log x_residual_term ib1.age_control##c.HasOtherBank, absorb(centuryyearpos#year bank#INS bank#year INS#year age_control) vce(cluster s_rrnr_id INS)
	estadd local gain 		"No":reg_search_borrower2
	estadd local oldrate   "Yes":reg_search_borrower2
	estadd local origxyear  "Yes":reg_search_borrower2
	estadd local bankxzip   "Yes":reg_search_borrower2
	estadd local bankxyear  "Yes":reg_search_borrower2
	estadd local INSxyear   "Yes":reg_search_borrower2
	estadd local agegroup	"Yes":reg_search_borrower2
	estadd local subsample  "No":reg_search_borrower2

esttab reg_borrower1 reg_borrower2 reg_borrower3 reg_search_borrower1 reg_search_borrower2 ///
	using "$path/OUTPUT TABLES/reg_borrower.tex", ///
b(%8.5f) ///
se(%8.5f) /// display standard errors instead of t-stats
star(* 0.10 ** 0.05 *** 0.01) /// display significance stars
legend /// adding legend
scalars("origxyear Additional controls?" "bankxzip Bank x municipality F.E.?" "bankxyear Bank x year F.E.?" "INSxyear Municipality x year F.E.?" "agegroup Borrower age group F.E.?" "subsample Subsample of refinancers?" "N Observations" "r2 R squared") /// scalars to be added to footer
sfmt(%8.3f  %8.3f  %8.3f %8.3f %8.3f %8.3f %15.0fc %8.3f ) /// format for scalars
compress /// reduce horizontal spacing
label /// make use of variable labels
noobs ///
mtitles (Internal External External "Consult (dummy)" "Consult (count)") /// model titles
keep (HasOtherBank *.age_control*) /// keep individual coefficients 
order(HasOtherBank *.age_control*) ///
replace // overwrite existing file

